﻿using DataAccess.DBConnections;
using DataAccess.ProxyConnections;
using Oracle.DataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess.Ejip
{
    public class BackoffcEjipConnection
    {
        DBConnectionsEngine dbEngine;
        OracleConnection Conn;
        Hashtable hashtable;
        string strSql;

        public BackoffcEjipConnection() 
        {

        }

        //select project_id,project_name from project
        public DataTable getProjectName()
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select project_id,project_id||' '||project_name as project_name,status from project
            ");

            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getProject(string project_id, string projStatus)
        {
            string param1 = "";
            string param2 = "";
            if (project_id != "") param1 = string.Format(" and proj.project_id = '{0}'", project_id);
            if (projStatus != "") param2 = string.Format(" and proj.status = '{0}'", projStatus);

            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            ROW_NUMBER() OVER(ORDER BY proj.project_id) AS row_number, 
            proj.project_id,
            proj.project_name,
            to_char(proj.project_start,'dd/mm/yyyy') as project_start,
            to_char(proj.project_end,'dd/mm/yyyy') as project_end,
            case
            when proj.status='A'  then  'Active'
            when proj.status='I'  then  'Inactive'
            when proj.status='C'  then  'Cancel'
            else 'X' end as status,
            proj.crdate,
            proj.crby,
            proj.upddate,
            proj.updby
            from project proj
            where 1=1
            {0}
            {1}
            ", param1, param2);

            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getProjectById(string project_id)
        {
            string param1 = "";
            if (project_id != "") param1 = string.Format(" and proj.project_id = '{0}'", project_id);

            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            ROW_NUMBER() OVER(ORDER BY proj.project_id) AS row_number, 
            proj.project_id,
            proj.project_name,
            to_char(proj.project_start,'dd/mm/yyyy') as project_start,
            to_char(proj.project_end,'dd/mm/yyyy') as project_end,
            case
            when proj.status='A'  then  'Active'
            when proj.status='I'  then  'Inactive'
            when proj.status='C'  then  'Cancel'
            else 'X' end as status,
            proj.crdate,
            proj.crby,
            proj.upddate,
            proj.updby
            from project proj
            where 1=1
            {0}
            ", param1);

            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getProjectDetail(int proj_id)
        {
            string param1 = "";
            if (proj_id != null) param1 = string.Format(" and pd.project_id = {0}", proj_id);
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            pd.project_detail_id,
            pd.percen_emp,
            pd.percen_com 
            from project_detail pd
            where 1=1 
            {0}
            order by pd.project_detail_id
            ", param1);

            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getProjectDetailToDropdownlist(string projectId)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            p_detail.project_detail_id,
            p_detail.project_id,
            'อัตราเงินสมทบส่วน พนง. '||p_detail.percen_emp||'% '||'อัตราเงินสมทบส่วน บริษัท.'||p_detail.percen_com||'%' as descript
            from project_detail p_detail
            where 1=1 
            and p_detail.project_id =  decode(:p1, '',p_detail.project_id, 'null', p_detail.project_id, :p1)
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", projectId);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        
        public DataTable getProjectEmployeeData(string projectId, string status,string emp_id)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            row_number() over(ordeR BY de_emp.emp_id) AS row_number, 
            de_emp.project_id,
            de_emp.project_detail_id,
            de_emp.emp_id,
            employ.emp_firstname||' '||employ.emp_lastname as emp_name,
            employ.emp_department,
            employ.emp_position,
            to_char(de_emp.start_date,'dd/mm/yyyy') as start_date,
            to_char(de_emp.end_date,'dd/mm/yyyy') as end_date,
            case
            when de_emp.status='A' then 'Active'
            when de_emp.status='I' then 'Inactive'
            when de_emp.status='C' then 'Cancel'
            else 'X' end as status,
            p_detail.percen_emp,
            p_detail.percen_com
            from project_detail_emp de_emp
            join employ on de_emp.emp_id = employ.emp_id 
            join project_detail p_detail on p_detail.project_id = de_emp.project_id
            where 1=1
            and p_detail.project_detail_id=de_emp.project_detail_id
            and de_emp.project_id =  decode(:p1, '',de_emp.project_id, 'null', de_emp.project_id, :p1)
            and de_emp.status =  decode(:p2, '',de_emp.status, 'null', de_emp.status, :p2)
            and de_emp.emp_id =  decode(:p3, '',de_emp.emp_id, 'null', de_emp.emp_id, :p3)
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", projectId);
            hashtable.Add(":p2", status);
            hashtable.Add(":p3", emp_id);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getProjectEmployeeData_ForReport()
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select project.project_id,
            project.project_name,
            project.project_start,
            project.project_end,
            project_detail_emp.start_date,
            project_cal.period_month||'/'||project_cal.period_year as period,
            project_detail.percen_emp,
            project_cal.amount_emp,
            project_detail.percen_com,
            project_cal.amount_com
            from project_detail_emp
            left join project on project.project_id = project_detail_emp.project_id
            left join project_cal on project_cal.emp_id = project_detail_emp.emp_id
            left join project_detail on project_detail.project_id = project.project_id
            ");

            return dbEngine.GetDataTable(strSql, Conn);
        }
        
        public DataTable getEmployDepartment()
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select employ.emp_department 
            from employ 
            group by employ.emp_department 
            ");
            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getEmployPosition(string department)
        {
            string param1 = "";
            if (department != null) param1 = string.Format(" and employ.emp_department ='{0}'", department);

            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select employ.emp_position 
            from employ 
            where 1=1
            {0}
            group by employ.emp_position
            ", param1);
            return dbEngine.GetDataTable(strSql, Conn);
        }
        public DataTable getEmploy(string empId, string Emp_name, string Emp_Sname, string department, string position, string dateStart,string group,string com_code,string status)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            ROW_NUMBER() OVER(ORDER BY employ.emp_id) AS row_number, 
            employ.emp_id,
            employ.emp_firstname||' '||employ.emp_lastname as emp_name, 
            employ.emp_department,
            employ.emp_position,
            to_char(employ.entry_date,'dd/mm/yyyy') as entry_date,
            case
            when employ.status='A' then 'Active'
            when employ.status='I' then 'Inactive'
            when employ.status='C' then 'Cancel'
            else 'X' end as status
            from employ 
            where 1=1
            
            and emp_id like decode(:p1, '',emp_id, 'null', emp_id, '%' || :p1 || '%')
            and emp_firstname =  decode(:p2, '',emp_firstname, 'null', emp_firstname, :p2)
            and emp_lastname =  decode(:p3, '',emp_lastname, 'null', emp_lastname, :p3)
            and emp_department =  decode(:p4, '',emp_department, 'null', emp_department, :p4)
            and emp_position =  decode(:p5, '',emp_position, 'null', emp_position, :p5)
            and to_char(entry_date,'dd/mm/yyyy') =  decode(:p6, '',to_char(entry_date,'dd/mm/yyyy'), null, to_char(entry_date,'dd/mm/yyyy'), :p6)
            and emp_group =  decode(:p7, '',emp_group, 'null', emp_group, :p7) 
            and status =  decode(:p8, '',status, 'null', status, :p8) 
            and company_code =  decode(:p9, '',company_code, 'null', company_code, :p9) 
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", empId);
            hashtable.Add(":p2", Emp_name);
            hashtable.Add(":p3", Emp_Sname);
            hashtable.Add(":p4", department);
            hashtable.Add(":p5", position);
            hashtable.Add(":p6", dateStart);
            hashtable.Add(":p7", group);
            hashtable.Add(":p8", status);
            hashtable.Add(":p9", com_code);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        public DataTable getEmploy(string empId, string group, string com_code)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            employ.emp_id,
            employ.emp_firstname,
            employ.emp_lastname, 
            employ.emp_department,
            employ.emp_position,
            to_char(employ.entry_date,'dd/mm/yyyy') as entry_date,
            employ.emp_salary,
            employ.company_code,
            employ.status
            from employ
            where 
            employ.company_code = 'HP00'
            and employ.emp_id =  decode(:p1, '',employ.emp_id, 'null', employ.emp_id, :p1)
            and employ.emp_group =  decode(:p2, '',employ.emp_group, 'null', employ.emp_group, :p2) 
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", empId);
            hashtable.Add(":p2", group);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }


        public DataTable getEmploy_InProject(string empId, string Emp_name, string Emp_Sname, string department, string position, string dateStart, string group, string project_id)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            ROW_NUMBER() OVER(ORDER BY employ.emp_id) AS row_number, 
            employ.emp_id,
            employ.emp_firstname||' '||employ.emp_lastname as emp_name, 
            employ.emp_department,
            employ.emp_position,
            to_char(employ.entry_date,'dd/mm/yyyy') as entry_date
            from employ 
            where 
            company_code = 'HP00'
            and employ.emp_id in 
            (
            select emp_id from project_detail_emp 
            where 1=1
            and project_id like decode(:p8, '',project_id, 'null', project_id, '%' || :p8 || '%')
            )

            and emp_id like decode(:p1, '',emp_id, 'null', emp_id, '%' || :p1 || '%')
            and emp_firstname =  decode(:p2, '',emp_firstname, 'null', emp_firstname, :p2)
            and emp_lastname =  decode(:p3, '',emp_lastname, 'null', emp_lastname, :p3)
            and emp_department =  decode(:p4, '',emp_department, 'null', emp_department, :p4)
            and emp_position =  decode(:p5, '',emp_position, 'null', emp_position, :p5)
            and to_char(entry_date,'dd/mm/yyyy') =  decode(:p6, '',to_char(entry_date,'dd/mm/yyyy'), null, to_char(entry_date,'dd/mm/yyyy'), :p6)
            and emp_group =  decode(:p7, '',emp_group, 'null', emp_group, :p7) 
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", empId);
            hashtable.Add(":p2", Emp_name);
            hashtable.Add(":p3", Emp_Sname);
            hashtable.Add(":p4", department);
            hashtable.Add(":p5", position);
            hashtable.Add(":p6", dateStart);
            hashtable.Add(":p7", group);
            hashtable.Add(":p8", project_id);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        public DataTable getEmploy_NotInProject(string empId, string Emp_name, string Emp_Sname, string department, string position, string dateStart, string group, string project_id)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            ROW_NUMBER() OVER(ORDER BY employ.emp_id) AS row_number, 
            employ.emp_id,
            employ.emp_firstname||' '||employ.emp_lastname as emp_name, 
            employ.emp_department,
            employ.emp_position,
            to_char(employ.entry_date,'dd/mm/yyyy') as entry_date
            from employ 
            where 
            company_code = 'HP00'
            and employ.emp_id not in 
            (
            select emp_id from project_detail_emp 
            where 1=1
            and project_id like decode(:p8, '',project_id, 'null', project_id, '%' || :p8 || '%')
            )

            and emp_id like decode(:p1, '',emp_id, 'null', emp_id, '%' || :p1 || '%')
            and emp_firstname =  decode(:p2, '',emp_firstname, 'null', emp_firstname, :p2)
            and emp_lastname =  decode(:p3, '',emp_lastname, 'null', emp_lastname, :p3)
            and emp_department =  decode(:p4, '',emp_department, 'null', emp_department, :p4)
            and emp_position =  decode(:p5, '',emp_position, 'null', emp_position, :p5)
            and to_char(entry_date,'dd/mm/yyyy') =  decode(:p6, '',to_char(entry_date,'dd/mm/yyyy'), null, to_char(entry_date,'dd/mm/yyyy'), :p6)
            and emp_group =  decode(:p7, '',emp_group, 'null', emp_group, :p7) 
            ");
            hashtable = new Hashtable();
            hashtable.Add(":p1", empId);
            hashtable.Add(":p2", Emp_name);
            hashtable.Add(":p3", Emp_Sname);
            hashtable.Add(":p4", department);
            hashtable.Add(":p5", position);
            hashtable.Add(":p6", dateStart);
            hashtable.Add(":p7", group);
            hashtable.Add(":p8", project_id);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        


        public void insertProject(string projectName, string projectStart, string projectEnd, string projectStatus,string userName)
        {
            //TODO Fix ตอนนี้ Fix Org ไว้เป็น HP00
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            insert into project(
            project_id,project_name,project_start,project_end,status,
            crdate,crby,company_code
            )VALUES(
            S_PROJECT_ID.nextval,
            '{0}',
            to_date('{1}','dd/mm/yyyy'),
            to_date('{2}','dd/mm/yyyy'),
            '{3}',
            sysdate,'{4}','HP00'
            )", projectName, projectStart, projectEnd, projectStatus, userName);

            dbEngine.ExecuteNonQuery(strSql,Conn);
        }

        public void insertProjectEmpDetail(string project_id, string project_detail_id, string emp_id, string start_date,string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            insert into project_detail_emp(
            project_id,project_detail_id,emp_id,start_date,status,
            crdate,crby
            )values(
            '{0}','{1}','{2}',to_date('{3}','dd/mm/yyyy'),'A',
            sysdate,'{4}'
            )", project_id, project_detail_id, emp_id, start_date, username);
            dbEngine.ExecuteNonQuery(strSql, Conn);
        }

        public void updateProjectEmpDetail(string project_id, string project_detail_id, string emp_id, string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            update project_detail_emp 
            set project_detail_id = '{1}',
            upddate = sysdate,
            updby = '{3}'
            where project_id = '{0}' 
            and emp_id = '{2}' 
            ", project_id, project_detail_id, emp_id, username);
            dbEngine.ExecuteNonQuery(strSql, Conn);
        }
        public void updateProjectEmptoInactive(string project_id, string emp_id, string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            update project_detail_emp
            set status = 'I',
            upddate = sysdate,
            updby = '{2}'
            where project_id = '{0}'
            and emp_id = '{1}'
            ", project_id, emp_id, username);
            dbEngine.ExecuteNonQuery(strSql, Conn);
        }
        public void updateEmptoInactive(string emp_id, string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            update employ
            set status = 'C',
            upddate = sysdate,
            updby = '{1}'
            where emp_id = '{0}'
            ", emp_id, username);
            dbEngine.ExecuteNonQuery(strSql, Conn);
        }



        #region EmployeeAllowance
        public DataTable isDateIsCalculated(string calMonth, string calYear, string emp_group)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select * from project_cal
            where 
            period_month = '{0}'
            and period_year = '{1}'
            and emp_group = '{2}'
            ", calMonth, calYear, emp_group);

            return dbEngine.GetDataTable(strSql, Conn);
        }

        public DataTable getCalculatedProject(string calMonth, string calYear, string emp_group)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select distinct
            project_cal.project_id,project.project_name
            from project_cal
            join project on project.project_id = project_cal.project_id
            where 
            project_cal.period_month = '{0}'
            and project_cal.period_year = '{1}'
            and project_cal.emp_group = '{2}'
            and project.company_code = 'HP00'
            ", calMonth, calYear, emp_group);

            return dbEngine.GetDataTable(strSql, Conn);
        }

        public DataTable getCalculatedProjectForExcel(string calMonth, string calYear, string emp_group)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            select 
            project.project_name,
            employ.emp_id,
            employ.emp_firstname||' '||employ.emp_lastname as emp_name,
            trim(to_char(project_cal.amount_emp, '9999999D99')) as amount_emp,
            trim(to_char(project_cal.amount_com, '9999999D99')) as amount_com
            from project_cal 
            left join project on project_cal.project_id = project.project_id
            left join employ on employ.emp_id = project_cal.emp_id
            where 
            project.company_code = 'HP00'
            and project_cal.period_month = '{0}'
            and project_cal.period_year = '{1}'
            and employ.emp_group = '{2}'
            order by employ.emp_id
            ", calMonth, calYear, emp_group);

            return dbEngine.GetDataTable(strSql, Conn);
        }

        public void updateCalculateAllowance(string period_month, string period_year, string username, string group)
        {
            //Todo Fix company_code = 'HP00'
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
            insert into project_cal (
            project_cal_id,project_id,project_detail_id,emp_id,amount_emp,	
            amount_com,period_month,period_year,
            crdate,crby,emp_group
            )
            select 
            s_project_cal_id.nextval,
            a.project_id,
            a.project_detail_id,
            a.emp_id,
            ceil(((b.emp_salary*c.percen_emp)/100)) as amount_emp,
            ceil(((b.emp_salary*c.percen_com)/100)) as amount_com,
            '{0}' as period_month,
            '{1}' as period_year,
            sysdate ,
            '{2}',
            '{3}' 
            from project_detail_emp a
            join employ b on a.emp_id = b.emp_id
            join project_detail c on a.project_id = c.project_id
            join project d on d.project_id = a.project_id
            where 
            a.status='A'
            and c.status='A'
            and a.project_id=c.project_id
            and a.project_detail_id = c.project_detail_id
            and a.emp_id=b.emp_id
            and d.company_code = 'HP00'
            ", period_month, period_year, username, group);

            dbEngine.ExecuteNonQuery(strSql, Conn);
        } 
        
        #endregion

        #region Employee
        public void insertEmployee(string emp_id, string emp_firstname, string emp_lastname, string entry_date, string emp_department, string emp_position, Decimal emp_salary, string company_code, string emp_group, string crby)
        {
            //TODO Fix ตอนนี้ Fix Org ไว้เป็น HP00
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
                insert into employ(
                emp_id,emp_firstname,emp_lastname,entry_date,emp_department,
                emp_position,emp_salary,company_code,emp_group,status,
                crdate,crby
                )values(
                '{0}','{1}','{2}',to_date('{3}','dd/mm/yyyy'),'{4}',
                '{5}',{6},'{7}','{8}','A',
                sysdate,'{9}')
            ", emp_id, emp_firstname, emp_lastname, entry_date, emp_department,
             emp_position, emp_salary, company_code, emp_group,
             crby
             );

            dbEngine.ExecuteNonQuery(strSql, Conn);
        }

        public void updateEmployee(string emp_firstname, string emp_lastname, string entry_date, string emp_department, string emp_position, Decimal emp_salary, string company_code, string emp_group, string updby, string emp_id, string status)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getBackoffcEjipConnection();
            strSql = String.Format(@"
                update employ
                set 
                emp_firstname = '{0}',
                emp_lastname = '{1}',
                entry_date = to_date('{2}','dd/mm/yyyy'),
                emp_department = '{3}',
                emp_position = '{4}',
                emp_salary = {5},
                company_code= '{6}',
                emp_group = '{7}',
                upddate = sysdate,
                updby = '{8}',
                status = '{10}'
                where emp_id = '{9}'
            ", emp_firstname, emp_lastname, entry_date, emp_department, emp_position,
             emp_salary, company_code, emp_group, updby, emp_id, status
             );

            dbEngine.ExecuteNonQuery(strSql, Conn);
        } 
        #endregion

    }
}
